Release 10.1A: OpenEdge Data Management:
DataServer for ORACLE

ORACLE views

ORACLE schema objects include views. A view is a presentation of data in one or more tables. Table 2–2 lists the level of support available for various types of views.

Table 2–2: Supported ORACLE views 

View	Supported 4GL
All columns from a single table SELECT cust-num, name, zip ... FROM customer	Full support for this view. Use the USE-INDEX option with FIND NEXT statements to get the same results you would expect from the Progress 4GL.
Some columns from a single table SELECT cust-num, name FROM customer	Full support for this view. Use the USE-INDEX option with FIND NEXT statements to get the same results you would expect from the Progress 4GL.
All columns from a single table with an expression SELECT cust-num, UPPER(name), zip ... WHERE zip < 20000 FROM customer	Full support for this view, except that you cannot update the column with the expression (name, in this example).
All columns from a single table with an aggregate or function SELECT cust-num, name, zip ... WHERE zip < 20000 FROM customer GROUP BY zip	The only supported statements are: FOR EACH ... NO-LOCK|SHARE-LOCK: FIND ... NO-LOCK|SHARE-LOCK:
Columns from multiple tables SELECT cust-num, name, zip, order-num FROM customer, order WHERE sales-rep = ’HXM’	The only supported statements are: FOR EACH ... NO-LOCK|SHARE-LOCK: FIND ... NO-LOCK|SHARE-LOCK:

ORACLE views appear as tables in the Data Dictionary’s table list for the schema image, not as views. The Data Dictionary’s SQL View Report does not list ORACLE or other non-OpenEdge views. Nor can you access them through the PRO/SQL menu functions.

In addition, the Progress 4GL does not allow you to undo the deletion of a record with a view name inside a subtransaction block, so you must perform the deletion inside a transaction block. If you delete a view in a subtransaction block and then try to undo the deletion later, the Progress 4GL returns a run-time error. See OpenEdge Development: Progress 4GL Handbook for information on subtransactions.

Multi-table views

The DataServer supports direct access to multi-table views. Use the following 4GL syntax to read rows from multi-table views:

FOR EACH view-name { NO-LOCK | SHARE-LOCK } :

You cannot use other Progress 4GL queries, such as the DEFINE QUERY, OPEN QUERY, GET, and DEFINE BROWSE statements, to access multi-table views.

The Progress 4GL cannot recognize whether a view in an ORACLE database is a multi-table view. Although the DataServer copies multi-table views into the schema image, the Progress 4GL returns run-time errors if you try to update them with an OpenEdge application.

Views containing aggregates

The DataServer supports access to columns in views that contain aggregates or functions only when the affected column has a name associated with it. Assign specific names to the columns when you define an ORACLE view. For example, the following SQL statement names a computed column in a view definition:

CREATE VIEW view-name AS SELECT COUNT(*) cust_count FROM CUSTOMER

Use the following 4GL syntax to read rows from views that contain aggregates or functions:

FOR EACH view-name { NO-LOCK | SHARE-LOCK } :

You can also access the view by using the RUN STORED-PROC send-sql-statement option to send an SQL statement to select the data from the view. You can access a view by using the send-sql-statement option without adding index definitions for the view in the schema holder. See the Chapter 2, "Initial Programming Considerations," for more information.